package SQLAPI;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * The SQL Class for the AnvilWeb project is used in tandem with the SQLBroker.
 * This class does all of the String handling before the query is passed to the
 * database. This ensures that inputs are sanitized before being run against the 
 * database. This is also where data is handled and sorted before being passed 
 * to the web interface. 
 * @author Scott Catsirelis - 000602705 SAIT Polytechnic
 */
public class SQL {
    
    private final SQLBroker sb;

    public SQL() {
        this.sb = new SQLBroker();
    }
    /**
     * This method takes the input from the login page(index.jsp) and checks it
     * for illegal or dangerous characters, as well as comparing the input against
     * web users in the database for a valid login.
     * 
     * @param username - Username submitted from the web interface.
     * @param password - Password submitted from the web interface.
     * @return boolean - True if login is valid, false if not.
     */
    private boolean checkLoginInfo(String username, String password)
    {
        boolean flag=true;
        final String PROTECTED ="<>\"/\\;',?%$#!^&*()";
        
        for(int i=0;i<PROTECTED.length();i++)
        {
            char a = PROTECTED.charAt(i);
            for(int x=0;x<username.length();x++)
            {
                char b = username.charAt(x);
                if(b==a)
                {
                    flag=false;
                }
            }
            
            if(flag==false)
            {
            return flag;
            }
        }
        
        try {
            String unHolder,passHolder;
            sb.connect();
            ResultSet rs=sb.getWebUserLogin();
            
            while(rs.next())
            {
                unHolder=rs.getString(1);
                passHolder=rs.getString(2);
                if(username.equals(unHolder))
                {
                    if(password.equals(passHolder))
                    {
                    sb.close();
                    return true;
                    }
                }
            }
         sb.close();   
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return false;
    }
    /**
     * Commits a login to the database as well as toggling the status flag in the 
     * web_user table to set the status to 0 or "offline". 
     * This method also commits a usage report for the user logging in. 
     * @param username String - Username submitted from the web interface.
     * @param password String - Password submitted from the web interface.
     * @return boolean - 
     */
    public boolean login(String username, String password)
    {
        boolean flag;
        flag = checkLoginInfo(username,password);
        if(flag==true)
        {
            sb.connect();
            int userid=sb.getUserId(username);
            sb.toggleFlag(userid);
            sb.insertUsageData(userid,"Logged In");
            sb.close();
        }
        return flag;
    }
    
    /**
     * Method to commit a logout usage report to the usage_report table for 
     * user logging out and toggles the status flag to 0 or offline.
     * @param username - String - Username stored in the session of the online user.
     */
    public void logout(String username)
    {
        sb.connect();
        int userid=sb.getUserId(username);
        sb.toggleFlag(userid);
        sb.insertUsageData(userid,"Logged Out");
        sb.close();
    }
    
    /**
     * Returns an ArrayList of String objects that are the headers from the web_user
     * table in the database.
     * @return - ArrayList - a list containing Strings that are the headers for
     * the web user table in the web interface.
     */
    public ArrayList getWebUserHeaders()
    {
        ArrayList<String> list = new ArrayList();
        String empid,email,pass,ldap,status;
        
        try{
            sb.connect();
            ResultSet rs=sb.getAllWebUsers();
            ResultSetMetaData rsmd = rs.getMetaData();

                empid=rsmd.getColumnLabel(1);
                list.add(empid);
                email=rsmd.getColumnLabel(2);
                list.add(email);
                pass=rsmd.getColumnLabel(3);
                list.add(pass);
                status=rsmd.getColumnLabel(4);
                list.add(status);

            sb.close();
            
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return list;
    }
    /**
     * Returns an ArrayList of WebUser that are all of the values from the 
     * web_user table in the database. 
     * @return - ArrayList - List of WebUser objects.
     */
    public ArrayList getWebUsers()
    {
        ArrayList<WebUser> list = new ArrayList();
        int empid,status;
        String pass,email;
        
        try{
            sb.connect();
            ResultSet rs=sb.getAllWebUsers();
            while(rs.next())
            {
                empid=rs.getInt(1);
                email=rs.getString(2);
                pass=rs.getString(3);
                status=rs.getInt(4);
                WebUser e = new WebUser(empid,email,pass,status);
                list.add(e);
            }
            sb.close();
            
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return list;
    }
    /**
     * Returns an ArrayList of UsageReport objects that are built directly from
     * the usage_report table in the database.
     * @return AraryList - list of UsageReport objects.
     */
    public ArrayList getUsageReports()
    {
        ArrayList<UsageReport> list = new ArrayList();
        String usageid,userid,time,message;
        try {
            sb.connect();
            ResultSet rs=sb.getUsageData();
            while(rs.next())
            {
                usageid=rs.getString(1);
                userid=rs.getString(2);
                time=rs.getString(3);
                message=rs.getString(4);
                UsageReport rpt = new UsageReport(usageid,userid,time,message);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return list;
    }
    /**
     * Returns an ArrayList containing TransactionReport objects from the 
     * transaction table in the database.
     * @return - ArrayList - A list of TransactionReport objects.
     */
    public ArrayList getTransactionReports()
    {
        ArrayList<TransactionReport> list = new ArrayList();
        try {
            String userid,transid,time,objName,type,usageid;
            sb.connect();
            ResultSet rs = sb.getTransactionData();
            while(rs.next())
            {
                transid=rs.getString(1);
                usageid=rs.getString(2);
                userid=rs.getString(3);
                time=rs.getString(4);
                objName=rs.getString(5);
                type=rs.getString(6);
                TransactionReport rpt = new TransactionReport(transid,usageid,userid,time,objName,type);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
            return list;
    }
    /**
     * Returns all Transactions in the database for the specified userID.
     * @param userID - ID of the user you wish to seek all Transaction reports for.
     * @return - ArrayList - List of TransactionReport objects from the transaction table.
     */
    public ArrayList getTransactionReports(int userID)
    {
        ArrayList<TransactionReport> list = new ArrayList();
        try {
            String userid,transid,time,objName,type,usageid;
            final String SQL = "SELECT * FROM TRANSACTION WHERE user_id="+userID;
            sb.connect();
            ResultSet rs = sb.executeQuery(SQL);
            while(rs.next())
            {
                transid=rs.getString(1);
                usageid=rs.getString(2);
                userid=rs.getString(3);
                time=rs.getString(4);
                objName=rs.getString(5);
                type=rs.getString(6);
                TransactionReport rpt = new TransactionReport(transid,usageid,userid,time,objName,type);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
            return list;
    }
    /**
     * Returns an ArrayList of UsageReport objects that are built from entries in
     * the usage_report table in the database for a specific userID.
     * @param userID - int - ID to fetch reports for.
     * @return - ArrayList - List of UsageReport objects.
     */
    public ArrayList getUsageReports(int userID)
    {
        ArrayList<UsageReport> list = new ArrayList();
        try {
            String usageid,userid,time,message;
            final String sql = "SELECT * FROM USAGE_REPORT WHERE user_id="+userID;
            sb.connect();
            ResultSet rs = sb.executeQuery(sql);
            while(rs.next())
            {
                usageid=rs.getString(1);
                userid=rs.getString(2);
                time=rs.getString(3);
                message=rs.getString(4);
                UsageReport rpt = new UsageReport(usageid,userid,time,message);
                list.add(rpt);
            }
            sb.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return list;
    }
    /**
     * Returns the email associated with the userID in the web_user table in the database.
     * 
     * @param userid - int - ID of the user you wish to fetch an email for.
     * @return String - the email address of the user. 
     */
    public String getEmail(int userid)
    {
        String email=null;
        try {
            final String SQL = "SELECT email FROM WEB_USER WHERE user_id="+userid;
            sb.connect();
            ResultSet rs = sb.executeQuery(SQL);
            while(rs.next())
            {
                email=rs.getString(1);
            }
            sb.close();
        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return email;
    }
    /**
     * Inserts an entry in to the transaction_detail table in the database.
     * @param web_user - int - userID of the web user that was modified.
     * @param CAO - ArrayList - List of ChangeObjects that need to be saved to the database.
     * @param objName - String - name of the object that was modified.
     */
    public void insertChangeIntoTransaction(int web_user, ArrayList<ChangeObject> CAO, String objName)
    {
        sb.connect();
        sb.insertTransactionChanges(web_user, CAO, objName);
        sb.close();
    }
    /**
     * Inserts an entry in to the transaction table in the database.
     * @param web_user - int - userID of the web user that was modified.
     * @param objName - String - name of the object that was modified.
     * @param type - String - Type of modification that was made to the object. 
     */
    public void insertIntoTransaction(int web_user, String objName, String type)
    {
        sb.connect();
        sb.insertTransaction(web_user, objName, type);
        sb.close();
    }
    /**
     * Inserts an entry in to the usage_report table in the database based on a
     * user ID and a message to log.
     * @param empid - ID of the web user to log a report for.
     * @param message - Message that will be logged with the report.
     */
    public void generateUsageReport(int empid, String message)
    {
        sb.connect();
        sb.insertUsageData(empid, message);
        sb.close();
    }
    /**
     * Returns an ArrayList of TransactionDetail objects based on a specified
     * transaction ID.
     * @param transid - int - Transaction ID of the user you wish to fetch 
     * TransactionDetail's for.
     * @return - ArrayList - List of TransactionDetail objects built from the database.
     */
    public ArrayList<TransactionDetail> getTransactionDetails(int transid)
    {
        ArrayList<TransactionDetail> list = new ArrayList();
        String objName,attribName,prev,newValue;
        try {
            sb.connect();
            ResultSet rs=sb.getTransactionDetails(transid);
            while(rs.next())
            {
                objName=rs.getString(2);
                attribName=rs.getString(3);
                prev=rs.getString(4);
                newValue=rs.getString(5);
                TransactionDetail td = new TransactionDetail(transid,objName,attribName,prev,newValue);
                list.add(td);
            }
            sb.close();

        } catch (SQLException ex) {
            System.out.println("Error Occured: Please contact the system Administrator.");
        }
        return list;
    }
}